<?php

class Mybuilder {

    /**
     * 
     * @var Mysql
     */
    private $_mysql;

    /**
     * 
     * @var array
     */
    private $_sqlData;

    /**
     * Mysql类的fetch函数类型
     * 
     * @var string
     */
    private $_fetchType;

    /**
     * 
     * @param Mysql $mysql
     */
    public function __construct($mysql, $fetchType, $table) {
        $this->_mysql = $mysql;
        $this->_fetchType = $fetchType;
        $this->_sqlData = array(
            'table' => $table,
            'field' => '*',
            'where' => '1 ',
        );
    }

    /**
     * 设置表名
     *
     * @param string $table
     * @return $this
     */
    public function table($table) {
        $this->_sqlData['table'] = $table;
        return $this;
    }

    /**
     * 设置字段
     *
     * @param string|array $fields 
     * @return $this
     */
    public function fields($fields) {
        $this->_sqlData['field'] = $this->_mysql->parseFields($fields);
        return $this;
    }

    /**
     * 设置主键字段名
     * 
     * 注意：如果设置了主键字段则 where() 的条件永远是 $priKey = <where>
     * 
     * @param string $priKey 字段名 
     * @return $this
     */
    public function primary($priKey) {
        $this->_sqlData['primary'] = $this->_mysql->parseFields($priKey);
        return $this;
    }

    /**
     * 设置查询条件
     *
     * 说明:$where 可以是字符串或数组,如果定义为数组则格式有如下两种:
     *      $where = array('id' => 1,
     *                     'name' => 'yuanwei');
     *      解析后条件为: "id=1 AND name='yuanwei'"
     *
     *      $where = array('id'=>array('>=' => 1),
     *                     'name'=>array('like' => '%yuanwei%'));
     *      解析后条件为: "id>=1 AND name LIKE '%yuanwei%'"
     * 
     *      $where = array('id'=>array('>=' => 1),
     *                     'name'=>array('not like' => '%yuanwei%'));
     *      解析后条件为: "id>=1 AND name NOT LIKE '%yuanwei%'"
     * 
     *      $where = array('id'=>array('is' => null),
     *                     'name'=>array('is not' => null));
     *      解析后条件为: "id IS null AND name IS NOT null"
     * 
     *      $where = array('id' => array('between' => '6', 'and' => '7'));
     *      解析后条件为: "id between '6' AND '7'"
     * 
     *      $where = array('id' => array('between' => 1, 'and' => 2));
     *      解析后条件为: "id BETWEEN 1 AND 2"
     * 
     *      $where = array('id' => array('in' => array(1, 2, 3, 4));
     *      解析后条件为: "id IN (1, 2, 3, 4)"
     * 
     *      $where = array('id' => array('in' => '1, 2, 3, 4');
     *      解析后条件为: "id IN (1, 2, 3, 4)"
     *
     * @param string|array $where
     * @param string $operator 运算符
     * @param string $append 是否是附加条件，如果不是附加条件则会清除之前设置的where条件
     * @return $this
     */
    public function where($where, $operator = 'AND', $append = true) {
        if ($where) {
            if ($append) {
                $this->_sqlData['where'] .= $this->_mysql->parseWhere($where, $operator, $append);
            } else {
                $this->_sqlData['where'] = $this->_mysql->parseWhere($where, $operator, $append);
            }
        }
        return $this;
    }

    /**
     * 设置Or查询条件
     * 
     * @param type $where
     * @param type $append
     * @return $this
     */
    public function whereOr($where, $append = true) {
        $this->where($where, 'OR', $append);
        return $this;
    }

    /**
     * 设置分组
     *
     * @param string $group
     * @return $this
     */
    public function group($group) {
        $this->_sqlData['group'] = $group;
        return $this;
    }

    /**
     * 设置 HAVING
     *
     * @param string $having
     * @return $this
     */
    public function having($having) {
        $this->_sqlData['having'] = $having;
        return $this;
    }

    /**
     * 设置排序
     *
     * @param string|array $field 字段
     * @param string $type 排序类型 asc,desc
     * @return $this
     */
    public function order($fields, $type = '') {
        if ($fields) {
            if (is_array($fields)) {
                $fieldList = $spr = '';
                foreach ($fields as $k => $v) {
                    if (in_array($v, array('', 'asc', 'desc'))) {
                        $fieldList .= $spr . $this->_mysql->parseField($k) . ' ' . $v;
                    }
                    $spr = ',';
                }
                $this->_sqlData['order'] = $fieldList;
            } else {
                if (in_array($type, array('', 'asc', 'desc'))) {
                    $this->_sqlData['order'] = $this->_mysql->parseField($fields) . ' ' . $type;
                }
            }
        }
        return $this;
    }

    /**
     * 设置 LIMIT
     *
     * @param int $offset 开始的行数
     * @param int $length 取多少行,如果为 null 设到 $offset 行
     * @return $this
     */
    public function limit($offset = null, $length = null) {
        $limit = $offset;
        if ($length !== null) {
            $limit .= ',' . $length;
        }
        $this->_sqlData['limit'] = $limit;
        return $this;
    }

    /**
     * 返回查询结果集
     *
     * @return mixed
     */
    public function result() {
        // 执行不同的方法
        $fetch = $this->_fetchType;

        // 生成 SQL
        $sql = 'SELECT ';
        $sql .= $this->_sqlData['field'];
        $sql .= ' FROM ';
        $sql .= $this->_sqlData['table'];
        // 如果设置了主键则值密码是字符或数值
        if (isset($this->_sqlData['where'])) {
            $sql .= ' WHERE ';
            if (isset($this->_sqlData['primary'])) {
                $sql .= $this->_sqlData['primary'] . "='{$this->_sqlData['where']}'";
            } else {
                $sql .= $this->_sqlData['where'];
            }
        }
        if (isset($this->_sqlData['group'])) {
            $sql .= ' GROUP BY ' . $this->_sqlData['group'];
        }
        if (isset($this->_sqlData['having'])) {
            $sql .= ' HAVING ' . $this->_sqlData['having'];
        }
        if (isset($this->_sqlData['order'])) {
            $sql .= ' ORDER BY ' . $this->_sqlData['order'];
        }
        if (isset($this->_sqlData['limit'])) {
            $sql .= ' LIMIT ' . $this->_sqlData['limit'];
        } else {
            if ($fetch == 'fetchRow' || $fetch == 'fetchOne') {
                $sql .= ' LIMIT 1';
            }
        }
        return $this->_mysql->{$fetch}($sql);
    }

}

/**
 * 数据库操作类
 */
class Mysql {

    /**
     * 数据库配置
     * 
     * @var array
     */
    private $_config = array();

    /**
     * 执行时长
     * 
     * @var int
     */
    private $_totalTime = 0;

    /**
     * 数据库连接句柄
     *
     * @var mysqli
     */
    private $_link = null;

    /**
     * 事务计数器
     * 
     * @var int
     */
    private $_transaction_counter = 0;

    /**
     * 查询句柄
     *
     * @var mysqli_result
     */
    private $_query = null;

    /**
     * 最后一次执行的SQL语句
     * 
     * @var string 
     */
    private $_lastSql = '';

    /**
     * 结果集的模式
     *
     */
    const FETCH_MODE = MYSQLI_ASSOC;

    /**
     * 构造函数
     *
     * @param array $config = array(
      // 主机名或IP
      'host'=>'localhost',
      // 端口,默认为 3306
      'port'=>3306,
      // 数据库字符集
      'charset'=>'utf8',
      // 用户名
      'username'=>'root',
      // 密码
      'password'=>'root',
      //数据库名
      'database'=>'test',
      )
     * @param boolean $showError 是否显示错误信息
     */
    public function __construct($config = array()) {

        // 检测是否安装了 MYSQLI 扩展
        if (!function_exists('mysqli_connect')) {
            die('No mysqli extension.');
        }

        $this->_config = $config;

        // 连接DB
        $this->connect();
    }

    private function connect() {
        if ($this->_config['debug']) {
            $beginTime = microtime(true);
            $this->_link = @mysqli_connect(
                            $this->_config['host'],
                            $this->_config['username'],
                            $this->_config['password'],
                            $this->_config['database'],
                            $this->_config['port']
            );
            $endTime = microtime(true);
            // 记录调试信息
            $this->_setDebugInfo('Connect DB Server.', $endTime - $beginTime);
        } else {
            $this->_link = @mysqli_connect(
                            $this->_config['host'],
                            $this->_config['username'],
                            $this->_config['password'],
                            $this->_config['database'],
                            $this->_config['port']
            );
        }

        // 是否连接成功
        if (!$this->_link) {
            $this->_DBError('Cannot connect to mysql server: ' . mysqli_connect_error());
        } else {
            // 设置字符集
            $this->_setCharset();
        }
    }

    /**
     * 设置数据库连接字符集
     *
     */
    private function _setCharset() {
        $this->query('SET NAMES ' . $this->_config['charset']);
    }

    /**
     * 记录SQL调试时间
     *
     * @param string $sql 执行的SQL语句
     * @param float $execTime 执行的时间
     */
    private function _setDebugInfo($sql, $execTime) {

        if (!class_exists('Registry', false)) {
            return;
        }

        // 总时间累加
        $this->_totalTime += $execTime;
        // 是否设置了调试选项
        $cfgObj = Registry::getInstance();
        if (!$cfgObj->keyExists('DB_DEBUG')) {
            $cfgObj->set('DB_DEBUG', array(
                array(
                    'sql' => $sql,
                    'execTime' => $execTime,
                    'totalTime' => $this->_totalTime,
                ),
            ));
        } else {
            $data = $cfgObj->get('DB_DEBUG');
            $data[] = array(
                'sql' => $sql,
                'execTime' => $execTime,
                'totalTime' => $this->_totalTime,
            );
            $cfgObj->set('DB_DEBUG', $data);
        }
    }

    /**
     * 数据库连接或查询错误处理
     *
     * @param string $errorMsg 
     */
    private function _DBError($errorMsg) {
        // 如果显示错误则直接抛出异常
        if ($this->_config['display_error']) {
            throw new Exception($errorMsg);
        } else {
            Sys::log($errorMsg, 'db');
        }
    }

    /**
     * 返回当前错误序号
     *
     * @return int
     */
    public function errorNo() {
        return @mysqli_errno($this->_link);
    }

    /**
     * 返回当前错误信息
     *
     * @return string
     */
    public function errorMsg() {
        return @mysqli_error($this->_link);
    }

    /**
     * 关闭连接
     */
    public function close() {
        ($this->_query instanceof mysqli_result) && @mysqli_free_result($this->_query);
        $this->_link && @mysqli_close($this->_link);
        return true;
    }

    /**
     * 设置表前缀
     * 
     * @param string $prefix
     */
    public function setTablePre($prefix = '') {
        $this->_config['tablepre'] = $prefix;
    }

    /**
     * 返回带前缀的表名
     * 
     * @param string $table
     * @return string
     */
    public function tablename($table) {
        return $this->_config['tablepre'] . $table;
    }

    /**
     * 格式化用于数据库的字符串
     *
     * @param string $str
     * @return string
     */
    public function escape($str, $quote = true) {
        $str = @mysqli_real_escape_string($this->_link, $str);
        if ($quote) {
            return "'$str'";
        } else {
            return $str;
        }
    }

    /**
     * 开始事务
     * @param string $isolation 隔离级别
     * read uncommitted
     * read committed
     * repeatable read
     * serializable
     * @return bool
     */
    public function beginTransaction($isolation = '') {
        if ($isolation) {
            if (!$this->query("set session transaction isolation level {$isolation}")) {
                return false;
            }
        }
        $result = mysqli_autocommit($this->_link, false);
        if ($result) {
            $this->_transaction_counter++;
        }
        return $result;
    }

    /**
     * 提交事务
     * 
     * @return bool
     */
    public function commit($dec_transaction_counter = true) {
        $result = mysqli_commit($this->_link);
        if ($result) {
            if ($dec_transaction_counter) {
                $this->_transaction_counter--;
            }
            if ($this->_transaction_counter <= 0) {
                return mysqli_autocommit($this->_link, true);
            }
            return true;
        }
        return false;
    }

    /**
     * 事务回滚
     * 
     * @return bool
     */
    public function rollBack($dec_transaction_counter = true) {
        $result = mysqli_rollback($this->_link);
        if ($result) {
            if ($dec_transaction_counter) {
                $this->_transaction_counter--;
            }
            if ($this->_transaction_counter <= 0) {
                return mysqli_autocommit($this->_link, true);
            }
            return true;
        }
        return false;
    }

    /**
     * 执行 SQL
     *
     * @param string $sql
     */
    private function query($sql, $unbuffer = false) {

        if ($unbuffer) {

            $result = @mysqli_real_query($this->_link, $sql);

            if (!$result) {

                $error_no = $this->errorNo();
                if ($error_no == 2013 || $error_no == 2006) { // CR_SERVER_LOST，重连一次
                    $this->connect();

                    $result = @mysqli_real_query($this->_link, $sql);

                    if (!$result) {
                        $this->_DBError('SQL query error: [' . $sql . '] ' . $this->errorMsg());
                    }
                } else {
                    $this->_DBError('SQL query error: [' . $sql . '] ' . $this->errorMsg());
                }
            }

            if ($result) {
                $this->_query = @mysqli_use_result($this->_link);
            }

            return $result;
        } else {

            $this->_query = @mysqli_query($this->_link, $sql);

            if (!$this->_query) {

                $error_no = $this->errorNo();

                if ($error_no == 2013 || $error_no == 2006) { // CR_SERVER_LOST，重连一次
                    $this->connect();

                    $this->_query = @mysqli_query($this->_link, $sql);

                    if (!$this->_query) {

                        $this->_DBError('SQL query error: [' . $sql . '] ' . $this->errorMsg());
                    }
                } else {
                    $this->_DBError('SQL query error: [' . $sql . '] ' . $this->errorMsg());
                }
            }
            return $this->_query ? true : false;
        }
    }

    /**
     * 返回连接对象
     * 
     * @return mysqli
     */
    public function link() {
        return $this->_link;
    }

    /**
     * 返回查询游标
     * 
     * @return mysqli_result
     */
    public function cursor() {
        return $this->_query;
    }

    /**
     * 组合各种条件查询并返回结果集
     *
     * @param string || array $fields :字段名 'uid,name' || array('uid','name') 支持别名方式
     * @param string $table :表
     * @param mixed $where   :条件
     * @param string $order  :排序字段
     * @param string $limit  :返回记录行,格式 "0,10"
     * @param string $group  :分组字段
     * @param string $having :筛选条件
     * @return array
     */
    public function select($fields, $table, $where = '', $order = '', $limit = '', $group = '', $having = '') {

        $table && $this->_config['tablepre'] && $table = $this->_config['tablepre'] . $table;

        $sql = 'SELECT ' . $this->parseFields($fields) . ' FROM ' . $table;
        if ($where) {
            $sql .= ' WHERE ' . $this->parseWhere($where);
        }
        if ($group != '') {
            $sql .= ' GROUP BY ' . $group;
        }
        if ($having != '') {
            $sql .= ' HAVING ' . $having;
        }
        if ($order != '') {
            $sql .= ' ORDER BY ' . $order;
        }
        if ($limit != '') {
            $sql .= ' LIMIT ' . $limit;
        }
        return $this->fetchAll($sql);
    }

    /**
     * 执行任何SQL语句
     * 
     * 说明:$sql语句中可以传参数,格式如:"select * from user where userid=:uid and username=:name" 其中: ":uid"和":name" 表示参数变量
     *     则必需定义$bind为: $bind=array('uid'=>3,
     *                                  'name'=>'yuanwei')
     *     表示$sql中 :uid 的值为3, :name 的值为'yuanwei'
     *
     * 注意:SQL中的参数只能用于 WHERE 条件中
     *
     * @param string $sql
     * @param array $bind
     * @param bool $unbuffer 是否关闭查询缓存，缓存模式下会将查询数据全部加载到PHP内存中，造成PHP占用大量内存
     * @return bool
     */
    public function execute($sql, $bind = array(), $unbuffer = false) {
        $sql = $this->_parseBind($sql, $bind);
        $this->_lastSql = $sql;

        // 如果打开了调试
        if ($this->_config['debug']) {
            $beginTime = microtime(true);

            $bool = $this->query($sql, $unbuffer);

            $endTime = microtime(true);

            // 记录调试信息
            $this->_setDebugInfo($sql, $endTime - $beginTime);
        } else {
            $bool = $this->query($sql, $unbuffer);
        }
        return $bool;
    }

    /**
     * 更新记录,执行 UPDATE 操作
     *
     * 说明: $arrSets 格式如下:
     *      $arrSets = array('uid'=>1,
     *                       'name'=>'yuanwei');
     *
     * 解析后SET为: "uid=1,name='yuanwei'"
     *
     * @param string $table  :表
     * @param array $arrSets :设置的字段值
     * @param mixed $where   :条件,详细请看 Select()成员
     * @param string $order  :排序字段
     * @param int $limit     :记录行
     * @param string $group  :分组字段
     * @return boolean
     */
    public function update($table, $arrSets, $where = '', $order = '', $limit = '', $group = '') {

        $table && $this->_config['tablepre'] && $table = $this->_config['tablepre'] . $table;

        $sqlSet = $this->_parseUpdateSet($arrSets);
        $sql = sprintf("UPDATE %s SET %s", $table, $sqlSet);
        if ($where)
            $sql .= ' WHERE ' . $this->parseWhere($where);
        if ($order != '')
            $sql .= ' ORDER BY ' . $order;
        if ($group != '')
            $sql .= ' GROUP BY ' . $group;
        if ($limit != '')
            $sql .= ' LIMIT ' . $limit;

        return $this->execute($sql);
    }

    /**
     * 插入记录,执行 INSERT 操作
     *
     * 说明:有关 $arrSets 数组的定义请看: Update()成员
     *
     * @param string $table  :表名
     * @param array $arrSets :插入的字段
     * @param boolean $replace : 是否采用 REPLACE INTO 的方式插入记录
     * @return int
     */
    public function insert($table, $arrSets, $replaceInto = false) {

        $table && $this->_config['tablepre'] && $table = $this->_config['tablepre'] . $table;

        $ret = $this->_parseInsertSet($arrSets);
        $insertOpt = $replaceInto ? 'REPLACE' : 'INSERT';
        $sql = sprintf("%s INTO %s(%s) VALUES(%s)", $insertOpt, $table, $ret['key'], $ret['val']);
        return $this->execute($sql);
    }

    /**
     * 删除记录,执行 DELETE 操作,返回删除的记录行数
     * 
     * @param string $table :表
     * @param mixed $where  :条件,详细请看 Select()成员
     * @param string $order :排序字段
     * @param string $limit :记录行
     * @param string $group :分组
     */
    public function delete($table, $where, $order = '', $limit = '', $group = '') {

        $table && $this->_config['tablepre'] && $table = $this->_config['tablepre'] . $table;

        $sql = "DELETE FROM $table";
        if ($where)
            $sql .= ' WHERE ' . $this->parseWhere($where);
        if ($order != '')
            $sql .= ' ORDER BY ' . $order;
        if ($group != '')
            $sql .= ' GROUP BY ' . $group;
        if ($limit != '')
            $sql .= ' LIMIT ' . $limit;
        return $this->execute($sql);
    }

    /**
     * 执行SQL并返回所有结果集
     *
     * 说明:有关 $sql和$bind 的用法请看 execute()
     *
     * @param string $sql
     * @param array $bind
     * @return array
     */
    public function fetchAll($sql, $bind = array()) {
        $result = array();
        $this->execute($sql, $bind);
        while ($row = @mysqli_fetch_array($this->_query, self::FETCH_MODE)) {
            $result[] = $row;
        }
        return $result;
    }

    /**
     * 执行SQL并返回所有结果集
     * 结果集是以第一个字段为key的数组
     *
     * @param string $sql
     * @param array $bind
     * @return array
     */
    public function fetchAssoc($sql, $bind = array()) {
        $result = array();
        $this->execute($sql, $bind);
        while ($row = @mysqli_fetch_array($this->_query, self::FETCH_MODE)) {
            $result[current($row)] = $row;
        }
        return $result;
    }

    /**
     * 执行SQL并返回单条记录
     *
     * @param string $sql
     * @param array $bind
     * @return array
     */
    public function fetchRow($sql, $bind = array()) {
        $this->execute($sql, $bind);
        return @mysqli_fetch_array($this->_query, self::FETCH_MODE);
    }

    /**
     * 执行SQL并返回单个字段的所有值
     *
     * @param string $sql
     * @param array $bind
     * @return array
     */
    public function fetchCol($sql, $bind = array()) {
        $result = array();
        $this->execute($sql, $bind);
        while ($row = @mysqli_fetch_array($this->_query, self::FETCH_MODE)) {
            $result[] = current($row);
        }
        return $result;
    }

    /**
     * 执行SQL并返回单条记录中的单个字段值
     *
     * @param string $sql
     * @param array $bind
     * @return mixed 
     */
    public function fetchOne($sql, $bind = array()) {
        $this->execute($sql, $bind);
        $row = @mysqli_fetch_array($this->_query, self::FETCH_MODE);
        if ($row) {
            return current($row);
        } else {
            return false;
        }
    }

    /**
     * 最后 DELETE UPDATE 语句所影响的行数
     *
     * @return int
     */
    public function affectedRows() {
        return @mysqli_affected_rows($this->_link);
    }

    /**
     * 返回调用当前查询后的结果集中的记录数
     */
    public function rowCount() {
        return @mysqli_num_rows($this->_query);
    }

    /**
     * 返回MYSQL系统中当前所有可用的数据库
     *
     * @return array
     */
    public function metaDatabases() {
        return $this->fetchCol('SHOW DATABASES');
    }

    /**
     * 返回数据库中所有的表,如果为空则返回当前数据库中所有的表名
     *
     * @param string $dbName :数据库名
     * @return array
     */
    public function metaTables($dbName = '') {

        $sql = "SHOW TABLES";
        if ($dbName != '')
            $sql .= ' FROM ' . $dbName;
        return $this->fetchCol($sql);
    }

    /**
     * 返回指定表的所有字段名
     *
     * @param string $table :表名
     * @return Array
     */
    public function metaColumnNames($table) {
        $sql = 'SHOW COLUMNS FROM ' . trim($table);
        return $this->fetchCol($sql);
    }

    /**
     * 清空表,执行 TRUNCATE TABLE 操作
     * 
     * @param string $table:表名称
     */
    public function clear($table) {

        $table && $this->_config['tablepre'] && $table = $this->_config['tablepre'] . $table;

        $sql = "TRUNCATE TABLE $table";
        return $this->execute($sql);
    }

    /**
     * 返回最后执行 Insert() 操作时表中有 auto_increment 类型主键的值
     *
     * @return int
     */
    public function lastInsertId() {
        /**
         *  [PHP手册]
         * 
         * 如果 AUTO_INCREMENT 的列的类型是 BIGINT，则 mysql_insert_id() 返回的值将不正确。
         * 可以在 SQL 查询中用 MySQL 内部的 SQL 函数 LAST_INSERT_ID() 来替代。
         * 
         */
        $id = $this->fetchOne('select last_insert_id()');
        return $id ? $id : 0;
    }

    /**
     * 返回最后执行的sql语句
     * 
     * @return string
     */
    public function getLastSQL() {
        return $this->_lastSql;
    }

    /**
     * 解析字段名,防止字段名是关键字
     */
    public function parseField($fieldName) {
        $fieldName = trim($fieldName);
        if (strstr($fieldName, ' ') === false) {
            $fieldName = '`' . $fieldName . '`';
        }
        return $fieldName;
    }

    /**
     * 解析SQL语句中的值定义
     *
     * @param string $sql
     * @param array $bind
     * @return string
     */
    private function _parseBind($sql, $bind = array()) {
        $searchArr = array();
        $replaceArr = array();
        if (is_array($bind) && count($bind)) {
            foreach ($bind as $k => $v) {
                $searchArr[] = ":$k";
                $replaceArr[] = $this->_returnValue($v);
            }
            $sql = str_replace($searchArr, $replaceArr, $sql);
        }
        return $sql;
    }

    /**
     * 根据值的类型返回SQL语句式的值
     *
     * @param unknown_type $val
     * @return unknown
     */
    private function _returnValue($val) {
        if (is_int($val) || is_float($val)) {
            return $val;
        } elseif ($val === null) {
            return 'null';
        } else {
            $val = addslashes($val);
            return "'$val'";
        }
    }

    /**
     * 解析查询的字段
     * @param array || string $fields : 字段数组或字符串
     */
    public function parseFields($fields) {
        $fieldList = $spr = '';
        if (is_array($fields) && $fields) {
            foreach ($fields as $field) {
                $fieldList .= $spr . $this->parseField($field);
                $spr = ',';
            }
        } else {
            $fieldList = $fields;
        }
        return $fieldList;
    }

    /**
     * 解析 SQL WHERE 条件
     *
     * 说明:$where 可以是字符串或数组,如果定义为数组则格式有如下两种:
     *      $where = array('id' => 1,
     *                     'name' => 'yuanwei');
     *      解析后条件为: "id=1 AND name='yuanwei'"
     *
     *      $where = array('id'=>array('>=' => 1),
     *                     'name'=>array('like' => '%yuanwei%'));
     *      解析后条件为: "id>=1 AND name LIKE '%yuanwei%'"
     * 
     *      $where = array('id'=>array('>=' => 1),
     *                     'name'=>array('not like' => '%yuanwei%'));
     *      解析后条件为: "id>=1 AND name NOT LIKE '%yuanwei%'"
     * 
     *      $where = array('id' => array('between' => '6', 'and' => '7'));
     *      解析后条件为: "id between '6' AND '7'"
     * 
     *      $where = array('id' => array('between' => 1, 'and' => 2));
     *      解析后条件为: "id BETWEEN 1 AND 2"
     * 
     *      $where = array('id' => array('in' => array(1, 2, 3, 4));
     *      解析后条件为: "id IN (1, 2, 3, 4)"
     * 
     *      $where = array('id' => array('in' => '1, 2, 3, 4');
     *      解析后条件为: "id IN (1, 2, 3, 4)"
     * 
     *      $where = array('id' => array('not in' => array(1, 2, 3, 4));
     *      解析后条件为: "id NOT IN (1, 2, 3, 4)"
     * 
     *      $where = array('id' => array('not in' => '1, 2, 3, 4');
     *      解析后条件为: "id NOT IN (1, 2, 3, 4)"
     *
     * @return string
     */
    public function parseWhere($where, $operator = 'AND', $append = false) {
        if ($append) {
            // 有append的where
            if (is_array($where)) {

                $sqlWhere = " {$operator} ( ";
                $first = true;

                foreach ($where as $k => $v) {
                    if ($first) {
                        $sqlWhere .= $this->parseField($k);
                        $first = false;
                    } else {
                        $sqlWhere .= " AND " . $this->parseField($k);
                    }

                    if (is_array($v)) {
                        foreach ($v as $_k => $_v) {
                            if (strtoupper($_k) == 'IN' || strtoupper($_k) == 'NOT IN') {
                                if (!is_array($_v)) {
                                    $_v = explode(',', $_v);
                                }
                                array_walk($_v, function (&$_vv) {
                                    $_vv = $this->_returnValue($_vv);
                                });
                                $sqlWhere .= sprintf(" %s ", strtoupper($_k)) . '(' . implode(', ', $_v) . ')';
                            } else {
                                $sqlWhere .= sprintf(" %s ", strtoupper($_k)) . $this->_returnValue($_v);
                            }
                        }
                    } else {
                        $sqlWhere .= '=' . $this->_returnValue($v);
                    }
                }

                $sqlWhere .= ' )';
            } else { // 字符串
                $sqlWhere = " {$operator} ( {$where} )";
            }
        } else {
            // 没有append的where
            if (is_array($where)) {

                $sqlWhere = '1 ';

                foreach ($where as $k => $v) {
                    $sqlWhere .= " AND " . $this->parseField($k);
                    if (is_array($v)) {
                        foreach ($v as $_k => $_v) {

                            if (strtoupper($_k) == 'IN' || strtoupper($_k) == 'NOT IN') {
                                if (!is_array($_v)) {
                                    $_v = explode(',', $_v);
                                }
                                array_walk($_v, function (&$_vv) {
                                    $_vv = $this->_returnValue($_vv);
                                });
                                $sqlWhere .= sprintf(" %s ", strtoupper($_k)) . '(' . implode(', ', $_v) . ')';
                            } else {
                                $sqlWhere .= sprintf(" %s ", strtoupper($_k)) . $this->_returnValue($_v);
                            }
                        }
                    } else {
                        $sqlWhere .= '=' . $this->_returnValue($v);
                    }
                }
            } else {
                $sqlWhere = $where;
            }
        }

        return $sqlWhere;
    }

    /**
     * 解析 UPDATE 操作字段设置
     *
     * @param array $arrSet
     * @return string
     */
    private function _parseUpdateSet($arrSet) {
        $sqlSet = $spr = '';
        if (is_array($arrSet)) {
            foreach ($arrSet as $k => $v) {
                $sqlSet .= $spr . $this->parseField($k) . '=' . $this->_returnValue($v);
                $spr = ',';
            }
        } else {
            $sqlSet = $arrSet;
        }
        return $sqlSet;
    }

    /**
     * 解析 INSERT 操作字段设置
     *
     * @param array $arrSet
     * @return array
     */
    private function _parseInsertSet($arrSet) {
        $Keys = $Values = $spr = '';
        foreach ($arrSet as $k => $v) {
            $Keys .= $spr . $this->parseField($k);
            $Values .= $spr . $this->_returnValue($v);
            $spr = ',';
        }
        return array('key' => $Keys, 'val' => $Values);
    }

    /**
     * 优化表,执行 OPTIMIZE TABLE 操作
     * 
     * @param string $table:表名称,如果表名空则优化库中所有的表
     */
    public function optimize($table = '') {

        $table && $this->_config['tablepre'] && $table = $this->_config['tablepre'] . $table;

        // 得到表
        if ($table == '') {
            $table = $this->fetchCol('SHOW TABLES');
        } else {
            $table = array($table);
        }
        // 优化表
        foreach ($table as $tab) {
            $sql = "OPTIMIZE TABLE $tab";
            $this->execute($sql);
        }
        return true;
    }

    /**
     * 修复表,执行 REPAIR TABLE 操作
     * 
     * @param string $table:表名称,如果表名空则修复库中所有的表
     */
    public function repair($table = '') {

        $table && $this->_config['tablepre'] && $table = $this->_config['tablepre'] . $table;

        // 得到表
        if ($table == '') {
            $table = $this->fetchCol('SHOW TABLES');
        } else {
            $table = array($table);
        }
        // 修复表
        foreach ($table as $tab) {
            $sql = "REPAIR TABLE $tab";
            $this->execute($sql);
        }
        return true;
    }

    /**
     * 初始化新的联级访问对象
     * 
     * @param $fetchType fetch类型
     * @param $table 表名，可以为空
     * 
     * @return Mybuilder
     */
    private function _queryObj($fetchType, $table = '') {
        $table = $this->_config['tablepre'] . $table;
        return new Mybuilder($this, $fetchType, $table);
    }

    /**
     * 初始化联级访问对象,得到 fetchAll() 结果集
     * 
     * 如 $db->getAll($table)->field('uid,username')->where('uid<4')->result();
     * @param string $table :表名,可以为空， 或者通过 ->table() 来指定
     * @return Mybuilder object
     */
    public function getAll($table = '') {
        return $this->_queryObj('fetchAll', $table);
    }

    /**
     * get系列函数,得到 fetchRow() 结果集,联级访问使用
     * 
     * 如 $db->getRow($table)->field('uid,username')->where('uid<4')->result();
     * @param string $table :表名,可以为空， 或者通过 ->table() 来指定
     * @return Mybuilder object
     */
    public function getRow($table = '') {
        return $this->_queryObj('fetchRow', $table);
    }

    /**
     * get系列函数,初始化联级访问对象,得到 fetchOne() 结果集
     * 
     * 如 $db->getOne($table)->field('uid,username')->where('uid<4')->result();
     * @param string $table :表名,可以为空， 或者通过 ->table() 来指定
     * @return Mybuilder object
     */
    public function getOne($table = '') {
        return $this->_queryObj('fetchOne', $table);
    }

    /**
     * get系列函数,初始化联级访问对象,得到 fetchCol() 结果集
     * 
     * 如 $db->getCol($table)->field('uid,username')->where('uid<4')->result();
     * @param string $table :表名,可以为空， 或者通过 ->table() 来指定
     * @return Mybuilder object
     */
    public function getCol($table = '') {
        return $this->_queryObj('fetchCol', $table);
    }

    /**
     * get系列函数,初始化联级访问对象,得到 fetchAssoc() 结果集
     * 
     * 如 $db->getAssoc($table)->field('uid,username')->where('uid<4')->result();
     * @param string $table :表名,可以为空， 或者通过 ->table() 来指定
     * @return Mybuilder object
     */
    public function getAssoc($table = '') {
        return $this->_queryObj('fetchAssoc', $table);
    }

    /**
     * get系列函数,初始化联级访问对象,得到 fetchOne() 结果集
     * 
     * 如 $db->count($table)->where('uid<4')->result();
     * @param string $table :表名,可以为空， 或者通过 ->table() 来指定
     * @return Mybuilder object
     */
    public function count($table = '') {
        return $this->_queryObj('fetchOne', $table)->fields('COUNT(*)');
    }

}
